﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Activities;
using System.Data;
using WFDataBaseProject.Helpers;
using System.Data.SqlClient;
using System.Diagnostics;
using System.ComponentModel;
using System.Activities.Presentation.Metadata;
using System.Activities.Presentation.PropertyEditing;    

namespace WFDataBaseProject
{

    [Designer(typeof(UCExecuteBulkCopyActivityDesigner))]
    public class ExecuteBulkCopyActivity:CodeActivity,IExecuteDBActivity
    {

        public ExecuteBulkCopyActivity()
        {
            AttributeTableBuilder builder = new AttributeTableBuilder();
            builder.AddCustomAttributes(typeof(ExecuteBulkCopyActivity), "ConnectionString",
                    new EditorAttribute(typeof(ConnectionStringEditor),
                    typeof(DialogPropertyValueEditor)));
            builder.AddCustomAttributes(typeof(ExecuteBulkCopyActivity), "DestinationConnectionString",
                    new EditorAttribute(typeof(ConnectionStringEditor),
                    typeof(DialogPropertyValueEditor)));
            builder.AddCustomAttributes(typeof(ExecuteBulkCopyActivity), "Query",
                    new EditorAttribute(typeof(QueryEditor),
                    typeof(DialogPropertyValueEditor)));
            MetadataStore.AddAttributeTable(builder.CreateTable());
        }

        #region arguments 
        [RequiredArgument]
        [Category("Inputs")]
        [Description("The connection string to connect to the data base")]
        public InArgument<string> ConnectionString { get; set; }
        [RequiredArgument]
        [Category("Inputs")]
        [Description("The connection type odbc,oledb or SQL Server connection")]
        public InArgument<TypeOfConnection> ConnectionType { get; set; }
        [RequiredArgument]
        [Category("Inputs")]
        [Description("The returned type scalar, record set or none")]
        public InArgument<TypeOfReturn> ReturnedType { get; set; }
        [Description("The query source whether inline, txt file or sql file ")]
        [Category("Inputs")]
        public InArgument<SqlSourceLocation> SourceLocation { get; set; }
        [RequiredArgument]
        [Category("Inputs")]
        [Description("The destination connection where the data will be stored")]
        public InArgument<string> DestinationConnectionString { get; set; }
        [RequiredArgument]
        [Category("Inputs")]
        [Description("The destination table where the data will be stored")]
        public InArgument<string> DestinationTable { get; set; }
        [RequiredArgument]
        [Category("Inputs")]
        [Description("The query body when the source location mode is InLine")]
        public InArgument<string> Query { get; set; }
        #endregion

        #region fields
         string connectionstring;
         TypeOfConnection connectionType;
         TypeOfReturn returnedType;
         SqlSourceLocation sourceLocation;
         string destinationConnectionString;
         string destinationTable;
         string query;

        #endregion
      
        protected override void Execute(CodeActivityContext context)
        {
            connectionstring = ConnectionString.Get<string>(context);
            connectionType = ConnectionType.Get<TypeOfConnection>(context);
            returnedType = ReturnedType.Get<TypeOfReturn>(context);
            sourceLocation = SourceLocation.Get<SqlSourceLocation>(context);
            destinationConnectionString = DestinationConnectionString.Get<string>(context);
            destinationTable = DestinationTable.Get<string>(context);
            query = Query.Get<string>(context);

            SqlConnection sqlconnection = new SqlConnection(connectionstring);
            
            SqlCommand sqlcommand;
            SqlDataReader reader;
            try
            {
                sqlconnection.Open();
                sqlcommand = new SqlCommand(query, sqlconnection);
                reader = sqlcommand.ExecuteReader();

                using (SqlConnection destinationConnection = new SqlConnection(destinationConnectionString))
                {
                    try
                    {
                        destinationConnection.Open();
                        using (SqlBulkCopy bulkcopy = new SqlBulkCopy(destinationConnection.ConnectionString))
                        {
                            bulkcopy.DestinationTableName = destinationTable;
                            try
                            {
                                bulkcopy.WriteToServer(reader);
                            }
                            catch (Exception caught)
                            {
#if DEBUG
                                Debug.Print(caught.Message);
#endif
                            }
                        }
                    }
                    catch (SqlException caught)
                    {
#if DEBUG
                        Debug.Print(caught.Message);
#endif

                    }
                    finally
                    {
                        destinationConnection.Close();
                    }
                }



            }
            catch (SqlException caught)
            {
#if DEBUG
                Debug.Print(caught.Message);
#endif

            }

            finally
            {
                sqlconnection.Close();
            }

        }
    }
}
